import pandas as pd
import pymysql
import bcrypt

sql = f"""
        INSERT INTO 
        ftprecode( 
            department, 
            employee, 
            account, 
            pwd,
            groupname
            ) 
    VALUES 
        (%s, %s, %s, %s, %s);
"""
conn = None
cursor = None


def _sql_init():
    """
    初始化数据库连接

    -------
    Returns
    -------
    conn:pymysql.connection
        数据库连接对象
    """
    usr = "root"
    passwd = "hdjt2022DATA!"
    host = "192.168.111.205"
    db = "tax_test"
    charset = "utf8"
    try:
        global conn, cursor
        if conn is None:
            conn = pymysql.connect(
                host=host,
                user=usr,
                password=passwd,
                database=db,
                charset=charset
            )
            # 创建游标对象
            cursor = conn.cursor()
    except BaseException:
        if cursor is not None:
            cursor.close()
        if conn is not None:
            conn.close()
        raise ValueError("连接异常！！！！！！！！！！！！！！！！！！！\n请联系数据人员！！！！！！！！！！！！！！！！！！！！")


def colse_commit():
    global conn, cursor
    conn.commit()
    cursor.close()
    conn.close()
    conn = None


def hash_password(pw):
    pwhash = bcrypt.hashpw(pw.encode('utf8'), bcrypt.gensalt())
    return pwhash.decode('utf8')


def main():
    df = pd.read_excel('ftp密码本.xlsx')
    df.fillna("", inplace=True)
    # df["密码"] = df["密码"].apply(hash_password)
    _sql_init()
    for i in df.index:
        print(df.iloc[i, -2])
        cursor.execute(sql, (
            df.loc[i, "部门"],
            df.loc[i, "员工"],
            df.loc[i, "账号"],
            df.loc[i, "密码"],
            df.loc[i, "组别"],
        ))
    # print(cursor.fetchall())
    # ((1, '8sdfsds', '8adasdasda', datetime.datetime(2024, 3, 13, 15, 37, 15, 207336)),)
    conn.commit()
    colse_commit()
    print("完成")


if __name__ == '__main__':
    main()
